package com.ttl.stock.db;


import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.GregorianCalendar;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;


import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import net.sf.jasperreports.engine.JRExporterParameter;
import net.sf.jasperreports.engine.JRRuntimeException;
import net.sf.jasperreports.engine.JasperFillManager;
import net.sf.jasperreports.engine.JasperPrint;
import net.sf.jasperreports.engine.JasperRunManager;
import net.sf.jasperreports.engine.export.JRXlsExporter;

import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionServlet;
import org.apache.struts.action.DynaActionForm;

import com.ttl.bc.report.receive.material.ReportReceiveMaterialBO;
import com.ttl.bc.report.receive.material.ReportReceiveMaterialDAO;
import com.ttl.common.util.CommonUtils;
import com.ttl.view.ITTL_OutStock;
import com.ttl.view.VMStockInOut;

public class StockBO {

	@SuppressWarnings({ "unchecked", "deprecation" })
	public boolean getMaterialOutStockReport(ActionForm form,
			HttpServletRequest request, HttpServletResponse response,
			ActionServlet servlet)  {
		
		String 	param_excel_out = (String)request.getParameter("param_excel_out");
		try {
			
			DynaActionForm mForm  = (DynaActionForm)form;		
			String[] startDate 	  = mForm.get("startDate").toString().split("-");
			String[] endDate 	  = mForm.get("endDate").toString().split("-");
			
//			System.out.println(startDate.toString()+"\t"+endDate.toString());
			
			//String	 itemCodeName = mForm.get("itemCodeName").toString();		

		    String sDate= startDate[2]+"-"+startDate[1]+"-"+startDate[0];
		    String eDate= endDate[2]+"-"+endDate[1]+"-"+endDate[0];    
		    //System.out.println("start getMaterialOutStockReport");
		    
		    String timeToRequestReport = CommonUtils.now();
		    new StockDAO().getMaterialOutStockReport(sDate, eDate, timeToRequestReport, "10");
		    
		    //System.out.println("end getMaterialOutStockReport");
		    Date sDate2= new Date(Integer.parseInt(startDate[2])-1900,Integer.parseInt(startDate[1])-1,Integer.parseInt(startDate[0]));
		    Date eDate2= new Date(Integer.parseInt(endDate[2])-1900,Integer.parseInt(endDate[1])-1,Integer.parseInt(endDate[0]));    
		    
		    //DateFormat df = new SimpleDateFormat("d MMMM yyyy");   ///DateFormat.getDateInstance(DateFormat.LONG,new Locale("en", "EN"));	    
		    
		    //Date a = df.parse(sDate2);
		    //System.out.println(df.format(sDate2));
		    
			HashMap hashMap = new HashMap();
			
			hashMap.put("startDate",sDate2);
			hashMap.put("endDate",eDate2);    
			hashMap.put("timeToRequestReport", timeToRequestReport);
			
			String jasperFile = "/ireport/rptMonthlyOutStock.jasper";
			System.out.println("it's ok jasper file");
			if(param_excel_out.equals("0")) {
				CommonUtils.generateReport(jasperFile, hashMap, response, servlet);
			}else {
				
				System.out.println("excel");
				
				jasperFile = "/ireport/rptMonthlyOutStock.jasper";
				File reportFile = new File(servlet.getServletContext().getRealPath(jasperFile));

			    if (!reportFile.exists())
			        throw new JRRuntimeException("File not found. The report design must be compiled first.");
			    		    

	 			/*hashMap = new HashMap();
				
				hashMap.put("monthly",""+mm);
				hashMap.put("yearly",ym[0]);			
				hashMap.put("monthName",monthName);    
				hashMap.put("timeToRequestReport", "");*/
				
			    try{
					Connection connection;
					ServletOutputStream servletOutputStream = response.getOutputStream();
					
					InputStream reportStream = servlet.getServletConfig()
										.getServletContext().getResourceAsStream(jasperFile);
					//@@@@@@@@@@
					InputStream propStream = servlet.getServletConfig()
								.getServletContext().getResourceAsStream("/config/config.properties");		
					Properties properties = new Properties();  
					properties.load(propStream);
					String database_server = properties.getProperty("database_server");
					String database_name = properties.getProperty("database_name");
					String port = properties.getProperty("port");
					String user = properties.getProperty("user");
					String password = properties.getProperty("password");
					//Constant.actionServlet = getServlet(); 
					//@@@@@@@@@@
					
					Class.forName("net.sourceforge.jtds.jdbc.Driver");
					connection = DriverManager.getConnection("jdbc:jtds:sqlserver://"+database_server+":"+port+"/"+database_name+";user="+user+";password="+password);
					//System.out.println("con ok");
					//JasperRunManager.runReportToPdfStream(reportStream, servletOutputStream, hashMap, connection);
					//System.out.println("jasper ok");    	
			    	 	
					 		    					    	
				    String path = reportFile.getAbsolutePath(); 
				    JasperPrint print = JasperFillManager.fillReport(path, hashMap, connection);
				     		
				    byte bytes[] = new byte[10];
				    String result = JasperRunManager.runReportToHtmlFile(servlet.getServletContext().getRealPath(jasperFile) , hashMap, connection);
				    				    
				    JRXlsExporter exporter = new JRXlsExporter();
				    
				    ByteArrayOutputStream xlsReport = new ByteArrayOutputStream();
				    exporter.setParameter(JRExporterParameter.JASPER_PRINT, print);
				    exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, xlsReport);
				    exporter.setParameter(JRExporterParameter.OUTPUT_FILE, "d:\\");
				    exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, "sample.xls");

 				    exporter.exportReport();
				    bytes = xlsReport.toByteArray();

				    response.setContentType("application/vnd.ms-excel");

				    response.setContentLength(bytes.length);
				    ServletOutputStream ouputStream = response.getOutputStream();
				    ouputStream.write(bytes, 0, bytes.length);
				    ouputStream.flush();
				    ouputStream.close();
				    
				    connection.close();
 					
					servletOutputStream.flush();
					servletOutputStream.close();
				
			    }catch(Exception e){ 
			    	System.out.print(e.toString());
			    }
			}
			
			return true;
		} catch (Exception e) {
			System.out.println(e.toString());
			return false;
		}
	}


	public ITTL_OutStock getITTL_OutStock(VMStockInOut v) {
		ITTL_OutStock os = new ITTL_OutStock();
		os.setDocDate(v.getDocDate());
		os.setDocNo(v.getDocNo());
		os.setItemName(v.getItemName());
		os.setItemCode(v.getItemCode().toUpperCase());
		os.setDescription(v.getRemark());
		os.setQtyMIN(v.getStockMin());
		os.setQtyMAX(v.getStockMax());
		
//		System.out.println("v.getAmount() ="+v.getAmount());
		double vGetAmount =0d;
		if(v.getAmount()==null) {
			//v.setAmount(0d);
			vGetAmount = 0d;
		}else {
			vGetAmount = v.getAmount().doubleValue();
		}
		
		if(v.getQty()!=null ) {
			if( v.getQty()>0) {
				os.setQtyIN(v.getQty());
				os.setQtyOUT(0d);
				
				java.text.DecimalFormat dfm = new java.text.DecimalFormat("0.0000");
				double d = new Double(dfm.format(vGetAmount/v.getQty())).doubleValue();
				os.setPrice( d );
				os.setAmountIN(vGetAmount);
			}else {
				os.setQtyIN(0d);
				os.setQtyOUT((-1)*v.getQty());
				
			}			
		}else {
			os.setQtyIN(0d);
			os.setQtyOUT(0d);
		}
		return os;
	}
	
	public ITTL_OutStock getITTL_OutStockOLD(VMStockInOut v) {
		ITTL_OutStock os = new ITTL_OutStock();
		os.setDocDate(v.getDocDate());
		os.setDocNo(v.getDocNo());
		os.setItemName(v.getItemName());
		os.setItemCode(v.getItemCode());
		os.setDescription(v.getRemark());
		os.setQtyMIN(v.getStockMin());
		os.setQtyMAX(v.getStockMax());
		if(v.getQty()!=null ) {
			if( v.getQty()>0) {
				os.setQtyIN(v.getQty());
				os.setQtyOUT(0d);
				
				java.text.DecimalFormat dfm = new java.text.DecimalFormat("0.0000");
				double d = new Double(dfm.format(v.getAmount()/v.getQty())).doubleValue();
				
				os.setPrice( d );
				os.setAmountIN(v.getAmount());
			}else {
				os.setQtyIN(0d);
				os.setQtyOUT((-1)*v.getQty());
				
			}			
		}else {
			os.setQtyIN(0d);
			os.setQtyOUT(0d);
		}
		
		return os;
	}

	@SuppressWarnings({ "unchecked", "rawtypes" })
	public boolean getMonthlyInventoryReport(ActionForm form,
			HttpServletRequest request, HttpServletResponse response,
			ActionServlet servlet) throws Exception {

		String reportStatus = (String)request.getParameter("reportStatus");
		String param_excel = (String)request.getParameter("param_excel");
		String param_excel_new = (String)request.getParameter("param_excel_new");
			
		try {
			DynaActionForm mForm  = (DynaActionForm)form;		
			String monthly 	  = mForm.get("monthly").toString();
			String yearly 	  = mForm.get("yearly").toString();
			
			String yearMonth = mForm.get("yearMonth").toString();
			
			String[] ym = yearMonth.split("-");
			@SuppressWarnings("unused")
			String	 itemCodeName = mForm.get("itemCodeName").toString();			    
		    String monthName = "";
	//		switch (Integer.parseInt(monthly)) {
			switch (Integer.parseInt(ym[1])+1) {
			case 1:	monthName = "มกราคม";	break;
			case 2:	monthName = "กุมภาพันธ์";	break;
			case 3:	monthName = "มีนาคม";	break;
			case 4:	monthName = "เมษายน";	break;
			case 5:	monthName = "พฤษภาคม";	break;
			case 6:	monthName = "มิถุนายน";	break;
			case 7:	monthName = "กรกฎาคม";	break;
			case 8:	monthName = "สิงหาคม";	break;
			case 9:	monthName = "กันยายน";	break;
			case 10:monthName = "ตุลาคม";	break;
			case 11:monthName = "พฤศจิกายน";	break;
			case 12:monthName = "ธันวาคม";	break;
			default:
				break;
			}
	
 			int mm = Integer.parseInt(ym[1])+1;			
 			int yyyy = Integer.parseInt(ym[0]);			
			
			Calendar cal = new GregorianCalendar(yyyy, Integer.parseInt(ym[1]), 1);
			int  days = cal.getActualMaximum(Calendar.DAY_OF_MONTH);     // 30  
			//System.out.println("days = "+days);
	//		String sDate = yearly +"-" + monthly +"-1";
	//		String eDate = yearly +"-" + monthly +"-"+days;
			String sDate = ym[0] +"-" + mm +"-1";
			String eDate = ym[0] +"-" + mm +"-"+days;
									
			if(reportStatus.equals("new")) {// ประมวลใหม่
 				String timeToRequestReport = CommonUtils.now(); 
				new StockDAO().getMaterialOutStockReport(sDate, eDate, timeToRequestReport, "10");		
//				new StockDAO().getMaterialOutStockReport2(sDate, eDate, timeToRequestReport, "10");
				HashMap hashMap = new HashMap();
				
	//			hashMap.put("monthly",monthly);
	//			hashMap.put("yearly",yearly);
				hashMap.put("monthly",""+mm);
				hashMap.put("yearly",ym[0]);			
				hashMap.put("monthName",monthName);    
				hashMap.put("timeToRequestReport", timeToRequestReport);
				System.out.println("before jasper file");
				String jasperFile = "/ireport/rptMonthlyStockReport.jasper";
				if(param_excel_new.equals("0")) {
					CommonUtils.generateReport(jasperFile, hashMap, response, servlet);
				}else {
					System.out.println("excel");
					
					jasperFile = "/ireport/rptMonthlyStockReport.jasper";
					File reportFile = new File(servlet.getServletContext().getRealPath(jasperFile));
	
				    if (!reportFile.exists())
				        throw new JRRuntimeException("File not found. The report design must be compiled first.");
				    		    
	
		 			hashMap = new HashMap();
					
					hashMap.put("monthly",""+mm);
					hashMap.put("yearly",ym[0]);			
					hashMap.put("monthName",monthName);    
					hashMap.put("timeToRequestReport", "");
					
				    try{
						Connection connection;
						ServletOutputStream servletOutputStream = response.getOutputStream();
						
						InputStream reportStream = servlet.getServletConfig()
											.getServletContext().getResourceAsStream(jasperFile);
						//@@@@@@@@@@
						InputStream propStream = servlet.getServletConfig()
									.getServletContext().getResourceAsStream("/config/config.properties");		
						Properties properties = new Properties();  
						properties.load(propStream);
						String database_server = properties.getProperty("database_server");
						String database_name = properties.getProperty("database_name");
						String port = properties.getProperty("port");
						String user = properties.getProperty("user");
						String password = properties.getProperty("password");
						//Constant.actionServlet = getServlet(); 
						//@@@@@@@@@@
						
						Class.forName("net.sourceforge.jtds.jdbc.Driver");
						connection = DriverManager.getConnection("jdbc:jtds:sqlserver://"+database_server+":"+port+"/"+database_name+";user="+user+";password="+password);
						//System.out.println("con ok");
						//JasperRunManager.runReportToPdfStream(reportStream, servletOutputStream, hashMap, connection);
						//System.out.println("jasper ok");    	
				    	 	
						 		    					    	
					    String path = reportFile.getAbsolutePath(); 
					    JasperPrint print = JasperFillManager.fillReport(path, hashMap, connection);
					     		
					    byte bytes[] = new byte[10];
					    String result = JasperRunManager.runReportToHtmlFile(servlet.getServletContext().getRealPath(jasperFile) , hashMap, connection);
					    				    
					    JRXlsExporter exporter = new JRXlsExporter();
					    
					    ByteArrayOutputStream xlsReport = new ByteArrayOutputStream();
					    exporter.setParameter(JRExporterParameter.JASPER_PRINT, print);
					    exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, xlsReport);
					    exporter.setParameter(JRExporterParameter.OUTPUT_FILE, "d:\\");
					    exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, "sample.xls");
	
	 				    exporter.exportReport();
					    bytes = xlsReport.toByteArray();
	
					    response.setContentType("application/vnd.ms-excel");
	
					    response.setContentLength(bytes.length);
					    ServletOutputStream ouputStream = response.getOutputStream();
					    ouputStream.write(bytes, 0, bytes.length);
					    ouputStream.flush();
					    ouputStream.close();
					    
					    connection.close();
	 					
						servletOutputStream.flush();
						servletOutputStream.close();
					
				    }catch(Exception e){ 
				    	System.out.print(e.toString());
				    }					
				}
			}else {  //เรียกของเดิม
				System.out.println("old "+reportStatus);
				HashMap hashMap = new HashMap();
				
				hashMap.put("monthly",""+mm);
				hashMap.put("yearly",ym[0]);			
				hashMap.put("monthName",monthName);    
				hashMap.put("timeToRequestReport", "");
				System.out.println("before jasper file");
				String jasperFile = "/ireport/rptMonthlyStockQueryReport.jasper";
				if(param_excel.equals("0")) {
					CommonUtils.generateReport(jasperFile, hashMap, response, servlet);
				}else {
					
					System.out.println("excel");
					
					jasperFile = "/ireport/rptMonthlyStockQueryReport.jasper";
					File reportFile = new File(servlet.getServletContext().getRealPath(jasperFile));
	
				    if (!reportFile.exists())
				        throw new JRRuntimeException("File not found. The report design must be compiled first.");
				    		    
	
		 			hashMap = new HashMap();
					
					hashMap.put("monthly",""+mm);
					hashMap.put("yearly",ym[0]);			
					hashMap.put("monthName",monthName);    
					hashMap.put("timeToRequestReport", "");
					
				    try{
						Connection connection;
						ServletOutputStream servletOutputStream = response.getOutputStream();
						
						InputStream reportStream = servlet.getServletConfig()
											.getServletContext().getResourceAsStream(jasperFile);
						//@@@@@@@@@@
						InputStream propStream = servlet.getServletConfig()
									.getServletContext().getResourceAsStream("/config/config.properties");		
						Properties properties = new Properties();  
						properties.load(propStream);
						String database_server = properties.getProperty("database_server");
						String database_name = properties.getProperty("database_name");
						String port = properties.getProperty("port");
						String user = properties.getProperty("user");
						String password = properties.getProperty("password");
						//Constant.actionServlet = getServlet(); 
						//@@@@@@@@@@
						
						Class.forName("net.sourceforge.jtds.jdbc.Driver");
						connection = DriverManager.getConnection("jdbc:jtds:sqlserver://"+database_server+":"+port+"/"+database_name+";user="+user+";password="+password);
						//System.out.println("con ok");
						//JasperRunManager.runReportToPdfStream(reportStream, servletOutputStream, hashMap, connection);
						//System.out.println("jasper ok");    	
				    	 	
						 		    					    	
					    String path = reportFile.getAbsolutePath(); 
					    JasperPrint print = JasperFillManager.fillReport(path, hashMap, connection);
					     		
					    byte bytes[] = new byte[10];
					    String result = JasperRunManager.runReportToHtmlFile(servlet.getServletContext().getRealPath(jasperFile) , hashMap, connection);
					    				    
					    JRXlsExporter exporter = new JRXlsExporter();
					    
					    ByteArrayOutputStream xlsReport = new ByteArrayOutputStream();
					    exporter.setParameter(JRExporterParameter.JASPER_PRINT, print);
					    exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, xlsReport);
					    exporter.setParameter(JRExporterParameter.OUTPUT_FILE, "d:\\");
					    exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, "sample.xls");
	
	 				    exporter.exportReport();
					    bytes = xlsReport.toByteArray();
	
					    response.setContentType("application/vnd.ms-excel");
	
					    response.setContentLength(bytes.length);
					    ServletOutputStream ouputStream = response.getOutputStream();
					    ouputStream.write(bytes, 0, bytes.length);
					    ouputStream.flush();
					    ouputStream.close();
					    
					    connection.close();
	 					
						servletOutputStream.flush();
						servletOutputStream.close();
					
				    }catch(Exception e){ 
				    	System.out.print(e.toString());
				    }
				}
			}
						
			return true;
		} catch (Exception e) {
			System.out.println(e.toString());
			return false;
		}
	}
	
	@SuppressWarnings("unchecked")
	public boolean getMonthlyInventoryReportOLD(ActionForm form,
			HttpServletRequest request, HttpServletResponse response,
			ActionServlet servlet) throws Exception {
		
			String reportStatus = (String)request.getParameter("reportStatus");
			String param_excel = (String)request.getParameter("param_excel");
			String param_excel_new = (String)request.getParameter("param_excel_new");
			
		try {
			DynaActionForm mForm  = (DynaActionForm)form;		
			String monthly 	  = mForm.get("monthly").toString();
			String yearly 	  = mForm.get("yearly").toString();
			
			String yearMonth = mForm.get("yearMonth").toString();
			
			String[] ym = yearMonth.split("-");
			//System.out.println(ym[0]+"    "+ym[1]);
			@SuppressWarnings("unused")
			String	 itemCodeName = mForm.get("itemCodeName").toString();		
	    
		    String monthName = "";
//			switch (Integer.parseInt(monthly)) {
			switch (Integer.parseInt(ym[1])+1) {
			case 1:	monthName = "มกราคม";	break;
			case 2:	monthName = "กุมภาพันธ์";	break;
			case 3:	monthName = "มีนาคม";	break;
			case 4:	monthName = "เมษายน";	break;
			case 5:	monthName = "พฤษภาคม";	break;
			case 6:	monthName = "มิถุนายน";	break;
			case 7:	monthName = "กรกฎาคม";	break;
			case 8:	monthName = "สิงหาคม";	break;
			case 9:	monthName = "กันยายน";	break;
			case 10:monthName = "ตุลาคม";	break;
			case 11:monthName = "พฤศจิกายน";	break;
			case 12:monthName = "ธันวาคม";	break;
			default:
				break;
			}

//			int mm = Integer.parseInt(monthly)-1;
			int mm = Integer.parseInt(ym[1])+1;			
//			int yyyy = Integer.parseInt(yearly);
			int yyyy = Integer.parseInt(ym[0]);			
			
			Calendar cal = new GregorianCalendar(yyyy, Integer.parseInt(ym[1]), 1);
			int  days = cal.getActualMaximum(Calendar.DAY_OF_MONTH);     // 30  
			//System.out.println("days = "+days);
//			String sDate = yearly +"-" + monthly +"-1";
//			String eDate = yearly +"-" + monthly +"-"+days;
			String sDate = ym[0] +"-" + mm +"-1";
			String eDate = ym[0] +"-" + mm +"-"+days;
			
  			
			
			if(reportStatus.equals("new")) {// ประมวลใหม่
				//System.out.println("new "+reportStatus);
				String timeToRequestReport = CommonUtils.now(); 
				new StockDAO().getMaterialOutStockReport(sDate, eDate, timeToRequestReport, "10");		
				
				HashMap hashMap = new HashMap();
				
	//			hashMap.put("monthly",monthly);
	//			hashMap.put("yearly",yearly);
				hashMap.put("monthly",""+mm);
				hashMap.put("yearly",ym[0]);			
				hashMap.put("monthName",monthName);    
				hashMap.put("timeToRequestReport", timeToRequestReport);
				System.out.println("before jasper file");
				String jasperFile = "/ireport/rptMonthlyStockReport.jasper";
				if(param_excel_new.equals("0")) {
					CommonUtils.generateReport(jasperFile, hashMap, response, servlet);
				}else {
					System.out.println("excel");
					
					jasperFile = "/ireport/rptMonthlyStockReport.jasper";
					File reportFile = new File(servlet.getServletContext().getRealPath(jasperFile));

				    if (!reportFile.exists())
				        throw new JRRuntimeException("File not found. The report design must be compiled first.");
				    		    

		 			hashMap = new HashMap();
					
					hashMap.put("monthly",""+mm);
					hashMap.put("yearly",ym[0]);			
					hashMap.put("monthName",monthName);    
					hashMap.put("timeToRequestReport", "");
					
				    try{
						Connection connection;
						ServletOutputStream servletOutputStream = response.getOutputStream();
						
						InputStream reportStream = servlet.getServletConfig()
											.getServletContext().getResourceAsStream(jasperFile);
						//@@@@@@@@@@
						InputStream propStream = servlet.getServletConfig()
									.getServletContext().getResourceAsStream("/config/config.properties");		
						Properties properties = new Properties();  
						properties.load(propStream);
						String database_server = properties.getProperty("database_server");
						String database_name = properties.getProperty("database_name");
						String port = properties.getProperty("port");
						String user = properties.getProperty("user");
						String password = properties.getProperty("password");
						//Constant.actionServlet = getServlet(); 
						//@@@@@@@@@@
						
						Class.forName("net.sourceforge.jtds.jdbc.Driver");
						connection = DriverManager.getConnection("jdbc:jtds:sqlserver://"+database_server+":"+port+"/"+database_name+";user="+user+";password="+password);
						//System.out.println("con ok");
						//JasperRunManager.runReportToPdfStream(reportStream, servletOutputStream, hashMap, connection);
						//System.out.println("jasper ok");    	
				    	 	
						 		    					    	
					    String path = reportFile.getAbsolutePath(); 
					    JasperPrint print = JasperFillManager.fillReport(path, hashMap, connection);
					     		
					    byte bytes[] = new byte[10];
					    String result = JasperRunManager.runReportToHtmlFile(servlet.getServletContext().getRealPath(jasperFile) , hashMap, connection);
					    				    
					    JRXlsExporter exporter = new JRXlsExporter();
					    
					    ByteArrayOutputStream xlsReport = new ByteArrayOutputStream();
					    exporter.setParameter(JRExporterParameter.JASPER_PRINT, print);
					    exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, xlsReport);
					    exporter.setParameter(JRExporterParameter.OUTPUT_FILE, "d:\\");
					    exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, "sample.xls");
	
	 				    exporter.exportReport();
					    bytes = xlsReport.toByteArray();
	
					    response.setContentType("application/vnd.ms-excel");
	
					    response.setContentLength(bytes.length);
					    ServletOutputStream ouputStream = response.getOutputStream();
					    ouputStream.write(bytes, 0, bytes.length);
					    ouputStream.flush();
					    ouputStream.close();
					    
					    connection.close();
	 					
						servletOutputStream.flush();
						servletOutputStream.close();
					
				    }catch(Exception e){ 
				    	System.out.print(e.toString());
				    }					
				}
			}else {  //เรียกของเดิม
				System.out.println("old "+reportStatus);
				HashMap hashMap = new HashMap();
				
				hashMap.put("monthly",""+mm);
				hashMap.put("yearly",ym[0]);			
				hashMap.put("monthName",monthName);    
				hashMap.put("timeToRequestReport", "");
				System.out.println("before jasper file");
				String jasperFile = "/ireport/rptMonthlyStockQueryReport.jasper";
				if(param_excel.equals("0")) {
					CommonUtils.generateReport(jasperFile, hashMap, response, servlet);
				}else {
					
					System.out.println("excel");
					
					jasperFile = "/ireport/rptMonthlyStockQueryReport.jasper";
					File reportFile = new File(servlet.getServletContext().getRealPath(jasperFile));

				    if (!reportFile.exists())
				        throw new JRRuntimeException("File not found. The report design must be compiled first.");
				    		    

		 			hashMap = new HashMap();
					
					hashMap.put("monthly",""+mm);
					hashMap.put("yearly",ym[0]);			
					hashMap.put("monthName",monthName);    
					hashMap.put("timeToRequestReport", "");
					
				    try{
						Connection connection;
						ServletOutputStream servletOutputStream = response.getOutputStream();
						
						InputStream reportStream = servlet.getServletConfig()
											.getServletContext().getResourceAsStream(jasperFile);
						//@@@@@@@@@@
						InputStream propStream = servlet.getServletConfig()
									.getServletContext().getResourceAsStream("/config/config.properties");		
						Properties properties = new Properties();  
						properties.load(propStream);
						String database_server = properties.getProperty("database_server");
						String database_name = properties.getProperty("database_name");
						String port = properties.getProperty("port");
						String user = properties.getProperty("user");
						String password = properties.getProperty("password");
						//Constant.actionServlet = getServlet(); 
						//@@@@@@@@@@
						
						Class.forName("net.sourceforge.jtds.jdbc.Driver");
						connection = DriverManager.getConnection("jdbc:jtds:sqlserver://"+database_server+":"+port+"/"+database_name+";user="+user+";password="+password);
						//System.out.println("con ok");
						//JasperRunManager.runReportToPdfStream(reportStream, servletOutputStream, hashMap, connection);
						//System.out.println("jasper ok");    	
				    	 	
						 		    					    	
					    String path = reportFile.getAbsolutePath(); 
					    JasperPrint print = JasperFillManager.fillReport(path, hashMap, connection);
					     		
					    byte bytes[] = new byte[10];
					    String result = JasperRunManager.runReportToHtmlFile(servlet.getServletContext().getRealPath(jasperFile) , hashMap, connection);
					    				    
					    JRXlsExporter exporter = new JRXlsExporter();
					    
					    ByteArrayOutputStream xlsReport = new ByteArrayOutputStream();
					    exporter.setParameter(JRExporterParameter.JASPER_PRINT, print);
					    exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, xlsReport);
					    exporter.setParameter(JRExporterParameter.OUTPUT_FILE, "d:\\");
					    exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, "sample.xls");
	
	 				    exporter.exportReport();
					    bytes = xlsReport.toByteArray();
	
					    response.setContentType("application/vnd.ms-excel");
	
					    response.setContentLength(bytes.length);
					    ServletOutputStream ouputStream = response.getOutputStream();
					    ouputStream.write(bytes, 0, bytes.length);
					    ouputStream.flush();
					    ouputStream.close();
					    
					    connection.close();
	 					
						servletOutputStream.flush();
						servletOutputStream.close();
					
				    }catch(Exception e){ 
				    	System.out.print(e.toString());
				    }
				}
			}
						
			return true;
		} catch (Exception e) {
			System.out.println(e.toString());
			return false;
		}		
	}


	public boolean getMonthlyInventoryReport2(ActionForm form,
			HttpServletRequest request, HttpServletResponse response,
			ActionServlet servlet) {
		String reportStatus = (String)request.getParameter("reportStatus");
		String param_excel = (String)request.getParameter("param_excel");
		String param_excel_new = (String)request.getParameter("param_excel_new");
			
		try {
			DynaActionForm mForm  = (DynaActionForm)form;		
			String monthly 	  = mForm.get("monthly").toString();
			String yearly 	  = mForm.get("yearly").toString();
			
			String yearMonth = mForm.get("yearMonth").toString();
			
			String[] ym = yearMonth.split("-");
			@SuppressWarnings("unused")
			String	 itemCodeName = mForm.get("itemCodeName").toString();			    
		    String monthName = "";
	//		switch (Integer.parseInt(monthly)) {
			switch (Integer.parseInt(ym[1])) {
			case 1:	monthName = "มกราคม";	break;
			case 2:	monthName = "กุมภาพันธ์";	break;
			case 3:	monthName = "มีนาคม";	break;
			case 4:	monthName = "เมษายน";	break;
			case 5:	monthName = "พฤษภาคม";	break;
			case 6:	monthName = "มิถุนายน";	break;
			case 7:	monthName = "กรกฎาคม";	break;
			case 8:	monthName = "สิงหาคม";	break;
			case 9:	monthName = "กันยายน";	break;
			case 10:monthName = "ตุลาคม";	break;
			case 11:monthName = "พฤศจิกายน";	break;
			case 12:monthName = "ธันวาคม";	break;
			default:
				break;
			}
	
 			int mm = Integer.parseInt(ym[1]);			
 			int yyyy = Integer.parseInt(ym[0]);			
			
 			//------------
 			SimpleDateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
 			String dateInString = "1/"+ym[1]+"/"+ym[0];
 			Date date = null;
 			try {
				date = formatter.parse(dateInString);
			} catch (Exception e) {
				e.printStackTrace();
			}
 			Calendar cal = Calendar.getInstance();
 			cal.setTime(date);
 			//------------
			//Calendar cal = new GregorianCalendar(yyyy, Integer.parseInt(ym[1]), 1);
			
			int  days = cal.getActualMaximum(Calendar.DAY_OF_MONTH);     // 30  
			//System.out.println("days = "+days);
	//		String sDate = yearly +"-" + monthly +"-1";
	//		String eDate = yearly +"-" + monthly +"-"+days;
			String sDate = ym[0] +"-" + mm +"-1";
			String eDate = ym[0] +"-" + mm +"-"+days;
									
			if(reportStatus.equals("new")) {// ประมวลใหม่
 				String timeToRequestReport = CommonUtils.now(); 
//				new StockDAO().getMaterialOutStockReport(sDate, eDate, timeToRequestReport, "10");		
//				new StockDAO().getMaterialOutStockReport2(sDate, eDate, timeToRequestReport, "10");
				HashMap hashMap = new HashMap();
				
	//			hashMap.put("monthly",monthly);
	//			hashMap.put("yearly",yearly);
				hashMap.put("monthly",""+mm);
				hashMap.put("yearly",ym[0]);			
				hashMap.put("monthName",monthName);    
				hashMap.put("timeToRequestReport", timeToRequestReport);
				System.out.println("before jasper file");
				String jasperFile = "/ireport/rptMonthlyStockReport2.jasper";
				if(param_excel_new.equals("0")) {
					CommonUtils.generateReport(jasperFile, hashMap, response, servlet);
										
					new ReportReceiveMaterialDAO().getReceiveByDepartment(ym[0], ym[1], "");
					
					//ReportReceiveMaterialBO bo = new ReportReceiveMaterialBO();
			 		//ArrayList<ITTL_OutStock> al = bo.getReceiveByDepartment(form,request,response, "/receiveMaterial.jasper");
					
				}else {
					System.out.println("excel");
					
					jasperFile = "/ireport/rptMonthlyStockReport2.jasper";
					File reportFile = new File(servlet.getServletContext().getRealPath(jasperFile));
	
				    if (!reportFile.exists())
				        throw new JRRuntimeException("File not found. The report design must be compiled first.");
				    		    
	
		 			hashMap = new HashMap();
					
					hashMap.put("monthly",""+mm);
					hashMap.put("yearly",ym[0]);			
					hashMap.put("monthName",monthName);    
					hashMap.put("timeToRequestReport", timeToRequestReport);
					
				    try{
						Connection connection;
						ServletOutputStream servletOutputStream = response.getOutputStream();
						
						InputStream reportStream = servlet.getServletConfig()
											.getServletContext().getResourceAsStream(jasperFile);
						//@@@@@@@@@@
						InputStream propStream = servlet.getServletConfig()
									.getServletContext().getResourceAsStream("/config/config.properties");		
						Properties properties = new Properties();  
						properties.load(propStream);
						String database_server = properties.getProperty("database_server");
						String database_name = properties.getProperty("database_name");
						String port = properties.getProperty("port");
						String user = properties.getProperty("user");
						String password = properties.getProperty("password");
						//Constant.actionServlet = getServlet(); 
						//@@@@@@@@@@
						
						Class.forName("net.sourceforge.jtds.jdbc.Driver");
						connection = DriverManager.getConnection("jdbc:jtds:sqlserver://"+database_server+":"+port+"/"+database_name+";user="+user+";password="+password);
						//System.out.println("con ok");
						//JasperRunManager.runReportToPdfStream(reportStream, servletOutputStream, hashMap, connection);
						//System.out.println("jasper ok");    	
				    	 	
						 		    					    	
					    String path = reportFile.getAbsolutePath(); 
					    JasperPrint print = JasperFillManager.fillReport(path, hashMap, connection);
					     		
					    byte bytes[] = new byte[10];
					    String result = JasperRunManager.runReportToHtmlFile(servlet.getServletContext().getRealPath(jasperFile) , hashMap, connection);
					    				    
					    JRXlsExporter exporter = new JRXlsExporter();
					    
					    ByteArrayOutputStream xlsReport = new ByteArrayOutputStream();
					    exporter.setParameter(JRExporterParameter.JASPER_PRINT, print);
					    exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, xlsReport);
					    exporter.setParameter(JRExporterParameter.OUTPUT_FILE, "d:\\");
					    exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, "sample.xls");
	
	 				    exporter.exportReport();
					    bytes = xlsReport.toByteArray();
	
					    response.setContentType("application/vnd.ms-excel");
	
					    response.setContentLength(bytes.length);
					    ServletOutputStream ouputStream = response.getOutputStream();
					    ouputStream.write(bytes, 0, bytes.length);
					    ouputStream.flush();
					    ouputStream.close();
					    
					    connection.close();
	 					
						servletOutputStream.flush();
						servletOutputStream.close();
					
				    }catch(Exception e){ 
				    	System.out.print(e.toString());
				    }					
				}
			}else {  //เรียกของเดิม
				//new ReportReceiveMaterialDAO().getReceiveByDepartment(ym[0], ym[1], "");
				
				System.out.println("old "+reportStatus);
				HashMap<String, String> hashMap = new HashMap<String, String>();
				
				hashMap.put("monthly",""+mm);
				hashMap.put("yearly",ym[0]);			
				hashMap.put("monthName",monthName);    
				hashMap.put("timeToRequestReport", "");
				System.out.println("before jasper file");
				String jasperFile = "/ireport/rptMonthlyStockQueryReport2.jasper";
				if(param_excel.equals("0")) {
					CommonUtils.generateReport(jasperFile, hashMap, response, servlet);
				}else {
					
					System.out.println("excel");
					
					jasperFile = "/ireport/rptMonthlyStockQueryReport2.jasper";
					File reportFile = new File(servlet.getServletContext().getRealPath(jasperFile));
	
				    if (!reportFile.exists())
				        throw new JRRuntimeException("File not found. The report design must be compiled first.");
				    		    
	
		 			hashMap = new HashMap();
					
					hashMap.put("monthly",""+mm);
					hashMap.put("yearly",ym[0]);			
					hashMap.put("monthName",monthName);    
					hashMap.put("timeToRequestReport", "");
					
				    try{
						Connection connection;
						ServletOutputStream servletOutputStream = response.getOutputStream();
						
						InputStream reportStream = servlet.getServletConfig()
											.getServletContext().getResourceAsStream(jasperFile);
						//@@@@@@@@@@
						InputStream propStream = servlet.getServletConfig()
									.getServletContext().getResourceAsStream("/config/config.properties");		
						Properties properties = new Properties();  
						properties.load(propStream);
						String database_server = properties.getProperty("database_server");
						String database_name = properties.getProperty("database_name");
						String port = properties.getProperty("port");
						String user = properties.getProperty("user");
						String password = properties.getProperty("password");
						//Constant.actionServlet = getServlet(); 
						//@@@@@@@@@@
						
						Class.forName("net.sourceforge.jtds.jdbc.Driver");
						connection = DriverManager.getConnection("jdbc:jtds:sqlserver://"+database_server+":"+port+"/"+database_name+";user="+user+";password="+password);
						//System.out.println("con ok");
						//JasperRunManager.runReportToPdfStream(reportStream, servletOutputStream, hashMap, connection);
						//System.out.println("jasper ok");    	
				    	 	
						 		    					    	
					    String path = reportFile.getAbsolutePath(); 
					    JasperPrint print = JasperFillManager.fillReport(path, hashMap, connection);
					     		
					    byte bytes[] = new byte[10];
					    String result = JasperRunManager.runReportToHtmlFile(servlet.getServletContext().getRealPath(jasperFile) , hashMap, connection);
					    				    
					    JRXlsExporter exporter = new JRXlsExporter();
					    
					    ByteArrayOutputStream xlsReport = new ByteArrayOutputStream();
					    exporter.setParameter(JRExporterParameter.JASPER_PRINT, print);
					    exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, xlsReport);
					    exporter.setParameter(JRExporterParameter.OUTPUT_FILE, "d:\\");
					    exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, "sample.xls");
	
	 				    exporter.exportReport();
					    bytes = xlsReport.toByteArray();
	
					    response.setContentType("application/vnd.ms-excel");
	
					    response.setContentLength(bytes.length);
					    ServletOutputStream ouputStream = response.getOutputStream();
					    ouputStream.write(bytes, 0, bytes.length);
					    ouputStream.flush();
					    ouputStream.close();
					    
					    connection.close();
	 					
						servletOutputStream.flush();
						servletOutputStream.close();
					
				    }catch(Exception e){ 
				    	System.out.print(e.toString());
				    }
				}
			}
						
			return true;
		} catch (Exception e) {
			System.out.println(e.toString());
			return false;
		}
	}


	public boolean monthlyClosed(ActionForm form, HttpServletRequest request,
			HttpServletResponse response, ActionServlet servlet) {

		String paramClosedStatus = request.getParameter("paramClosedStatus");				
		
		DynaActionForm mForm  = (DynaActionForm)form;						
		String yearMonth = mForm.get("yearMonth").toString();		
		String[] ym = yearMonth.split("-");		
		
		return new StockDAO().monthlyClosed(Integer.valueOf(paramClosedStatus), Integer.valueOf(ym[0]), Integer.valueOf(ym[1]));
	}


	public boolean getMaterialOutStockReport2(ActionForm form,
			HttpServletRequest request, HttpServletResponse response,
			ActionServlet servlet) {
		
		DynaActionForm mForm  = (DynaActionForm)form;						
		String yearMonth = mForm.get("yearMonth").toString();		
		String[] ym = yearMonth.split("-");
		
		String 	param_excel_out = (String)request.getParameter("param_excel_out");
		try {
					   
			HashMap<String, String> hashMap = new HashMap<String, String>();		
			hashMap.put("yearId",ym[0]);
			hashMap.put("monthId",ym[1]);    
			
			String monthName = "";
			switch (Integer.parseInt(ym[1])) {
			case 1:	monthName = "มกราคม";	break;
			case 2:	monthName = "กุมภาพันธ์";	break;
			case 3:	monthName = "มีนาคม";	break;
			case 4:	monthName = "เมษายน";	break;
			case 5:	monthName = "พฤษภาคม";	break;
			case 6:	monthName = "มิถุนายน";	break;
			case 7:	monthName = "กรกฎาคม";	break;
			case 8:	monthName = "สิงหาคม";	break;
			case 9:	monthName = "กันยายน";	break;
			case 10:monthName = "ตุลาคม";	break;
			case 11:monthName = "พฤศจิกายน";	break;
			case 12:monthName = "ธันวาคม";	break;
			default:
				break;
			}															
			hashMap.put("monthName", monthName+" "+ym[0]);
			String jasperFile = "/ireport/rptMonthlyOutStock5.jasper";
			System.out.println("it's ok jasper file");
			if(param_excel_out.equals("0")) {
				CommonUtils.generateReport(jasperFile, hashMap, response, servlet);
			}else {
				
				System.out.println("excel");
				
				//jasperFile = "/ireport/rptMonthlyOutStock.jasper";
				jasperFile = "/ireport/rptMonthlyOutStock5.jasper";
				File reportFile = new File(servlet.getServletContext().getRealPath(jasperFile));

			    if (!reportFile.exists())
			        throw new JRRuntimeException("File not found. The report design must be compiled first.");			    		  
				
			    try{
					Connection connection;
					ServletOutputStream servletOutputStream = response.getOutputStream();
					
					InputStream reportStream = servlet.getServletConfig()
										.getServletContext().getResourceAsStream(jasperFile);
					//@@@@@@@@@@
					InputStream propStream = servlet.getServletConfig()
								.getServletContext().getResourceAsStream("/config/config.properties");		
					Properties properties = new Properties();  
					properties.load(propStream);
					String database_server = properties.getProperty("database_server");
					String database_name = properties.getProperty("database_name");
					String port = properties.getProperty("port");
					String user = properties.getProperty("user");
					String password = properties.getProperty("password");
					//Constant.actionServlet = getServlet(); 
					//@@@@@@@@@@
					
					Class.forName("net.sourceforge.jtds.jdbc.Driver");
					connection = DriverManager.getConnection("jdbc:jtds:sqlserver://"+database_server+":"+port+"/"+database_name+";user="+user+";password="+password);
					 	 		    					    	
				    String path = reportFile.getAbsolutePath(); 
				    JasperPrint print = JasperFillManager.fillReport(path, hashMap, connection);
				     		
				    byte bytes[] = new byte[10];
				    String result = JasperRunManager.runReportToHtmlFile(servlet.getServletContext().getRealPath(jasperFile) , hashMap, connection);
				    				    
				    JRXlsExporter exporter = new JRXlsExporter();
				    
				    ByteArrayOutputStream xlsReport = new ByteArrayOutputStream();
				    exporter.setParameter(JRExporterParameter.JASPER_PRINT, print);
				    exporter.setParameter(JRExporterParameter.OUTPUT_STREAM, xlsReport);
				    exporter.setParameter(JRExporterParameter.OUTPUT_FILE, "d:\\");
				    exporter.setParameter(JRExporterParameter.OUTPUT_FILE_NAME, "sample.xls");

 				    exporter.exportReport();
				    bytes = xlsReport.toByteArray();

				    response.setContentType("application/vnd.ms-excel");

				    response.setContentLength(bytes.length);
				    ServletOutputStream ouputStream = response.getOutputStream();
				    ouputStream.write(bytes, 0, bytes.length);
				    ouputStream.flush();
				    ouputStream.close();
				    
				    connection.close();
 					
					servletOutputStream.flush();
					servletOutputStream.close();
				
			    }catch(Exception e){ 
			    	System.out.print(e.toString());
			    }
			}
			
			return true;
		} catch (Exception e) {
			System.out.println(e.toString());
			return false;
		}
	}	
}
